package usi.ch.onto.yago;

import java.io.BufferedWriter;
import java.io.File;
import java.io.FileWriter;
import java.io.IOException;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Collection;
import java.util.List;

import usi.ch.onto.lucene.scoring.IndexTermItem;


public class ExportResults {
	private final String CONNECTION_STRING = "jdbc:mysql://localhost/yago";
	private final String USER = "root";
	private final String PASS = "admin";

	private Connection conn;
	private final String RESULTS_TABLE_SUFIX = "results";

	public ExportResults() throws Exception {
		conn = DriverManager.getConnection(CONNECTION_STRING, USER, PASS);
		if (conn.isClosed())
			throw new Exception("Error connection to db ");
	}

	private String getExportQuerySQL(String tableName) {
		StringBuilder sb = new StringBuilder();

		sb.append("SELECT tab.id, tab.arg1, tab.arg2, tab.confidence ");
		sb.append(" INTO OUTFILE ?");
		sb.append(" FIELDS TERMINATED BY '\t' ");
		sb.append(" LINES TERMINATED BY '\n' FROM ");
		sb.append(tableName + RESULTS_TABLE_SUFIX);
		sb.append(" results, ");
		sb.append(tableName);
		sb.append(" tab");
		sb.append(" where tab.id = results.tabid and tab.FactName = ? and tab.extractorType = ?");

		return sb.toString();
	}

	private String getExportTaxonomyQuerySQL() {
		StringBuilder sb = new StringBuilder();

		sb.append("SELECT tab.id, tab.arg1, tab.arg2, tab.confidence ");
		sb.append(" INTO OUTFILE ?");
		sb.append(" FIELDS TERMINATED BY '\t' ");
		sb.append(" LINES TERMINATED BY '\n' FROM subclass tab");
		sb.append(", taxonomy tax ");
		sb.append(" where tab.id = tax.tabid and tab.FactName = ? and tab.extractorType = ?");

		return sb.toString();
	}

	private List<FactExtractor> getFactNamesFromTable(String tableName)
			throws SQLException {
		List<FactExtractor> list = new ArrayList<FactExtractor>();

		PreparedStatement pstmtExport = conn
				.prepareStatement("select distinct factName, extractorType from "
						+ tableName);

		ResultSet result = pstmtExport.executeQuery();
		while (result.next()) {

			list.add(new FactExtractor(result.getString(1), result.getInt(2)));
		}

		return list;
	}

	public static String combine(String path1, String path2) {
		File file1 = new File(path1);
		File file2 = new File(file1, path2);
		return file2.getPath();
	}

	/**
	 * exports a table that have a Results table associated i.e tab1 tab1results
	 * 
	 * @param tableName
	 * @param destinationDirectory
	 * @throws Exception
	 */
	public void exportNormalTable(String tableName, String destinationDirectory)
			throws Exception {
		PreparedStatement pstmtExport = conn
				.prepareStatement(getExportQuerySQL(tableName));

		System.out.println("exporting table " + tableName + " to directory "
				+ destinationDirectory);

		mysqlExportFile(pstmtExport, destinationDirectory, tableName);

	}

	public void exportTableTaxonomy(String destinationDirectory)
			throws SQLException {
		PreparedStatement pstmtExport = conn
				.prepareStatement(getExportTaxonomyQuerySQL());

		System.out.println("exporting table taxonomy to directory "
				+ destinationDirectory);

		mysqlExportFile(pstmtExport, destinationDirectory, "subclass");
	}

	private void mysqlExportFile(PreparedStatement pstmtExport,
			String destinationDirectory, String tableName) throws SQLException {
		String dir;
		List<FactExtractor> listFacts = getFactNamesFromTable(tableName);
		for (FactExtractor fact : listFacts) {
			System.out.println("factName " + fact.getFactName()
					+ " extractorType " + fact.getExtractorType());
			dir = combine(destinationDirectory, fact.getFactName());
			pstmtExport.setString(1, combine(dir, fact.getFileName()));
			pstmtExport.setString(2, fact.getFactName());
			pstmtExport.setInt(3, fact.getExtractorType());

			pstmtExport.execute();
		}
	}

	/**
	 * list originalList must have the same order as synonymList
	 * 
	 * @param destinationFilePath
	 * @param startCount
	 * @param originalList
	 * @param synonymList
	 * @throws Exception
	 */
	public static void exportSynonyms(String destinationFilePath,
			int startCount, List<String> originalList, List<String> synonymList, boolean removeduplicates)
			throws Exception {

		if (originalList.size() != synonymList.size())
			throw new Exception(
					"exportSynonyms originalList must have the same order and size of synonymList");

		BufferedWriter out = new BufferedWriter(new FileWriter(
				destinationFilePath));
		String line;
		for (int i = 0; i < originalList.size(); i++) {
			if (removeduplicates){
				if (originalList.get(i).equals(synonymList.get(i)) )
					continue;
			}
			line = startCount + "\t" + originalList.get(i) + "\t"
					+ synonymList.get(i) + "\t1.0";
			out.write(line);
			out.newLine();
			startCount++;
		}

		out.flush();
		out.close();

	}

	public static void outputTermItemsList(Collection<?> list, String outputFile) throws IOException{
		BufferedWriter out = new BufferedWriter(new FileWriter(outputFile));
		if (list.iterator().next() instanceof IndexTermItem)
			out.write(IndexTermItem.getHeader());
		out.newLine();
		for (Object iti : list){
			out.write(iti.toString());
			out.newLine();
		}		
		
		out.flush();
		out.close();
	}
	
}
